﻿CREATE PROCEDURE [util].[ConfigSQLAgentJobs]
@XmlData xml, @AppendToJob BIT=0
AS
BEGIN
	DECLARE @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlData

	-- ===============================================
	-- Add/Update Job Details
	-- ===============================================
	DECLARE C_JOBS CURSOR FOR(
		SELECT * FROM OPENXML(@docHandle, N'/Root/Job') 
		WITH (
			[Name] sysname
			,[Enabled] tinyint
			,[Description] nvarchar(512) 
			,[StartStepId] int
			,[CategoryName] sysname
			,[OwnerLoginName] sysname 
			,[NotifyLevelEventlog] int
			,[NotifyLevelEmail] int
			,[NotifyLevelNetsend] int
			,[NotifyLevelPage] int
			,[NotifyEmailOperatorName] sysname
			,[NotifyNetsendOperatorName] sysname
			,[NotifyPageOperatorName] sysname
			,[DeleteLevel] int
		)
	)
	OPEN C_JOBS
	DECLARE @Name sysname ,@Enabled tinyint ,@Description nvarchar(512) ,@StartStepId int, @CategoryName sysname, @OwnerLoginName sysname
	,@NotifyLevelEventlog int ,@NotifyLevelEmail int ,@NotifyLevelNetsend int ,@NotifyLevelPage int
	,@NotifyEmailOperatorName sysname ,@NotifyNetsendOperatorName sysname ,@NotifyPageOperatorName sysname
	,@DeleteLevel int

	FETCH NEXT FROM C_JOBS INTO @Name ,@Enabled ,@Description ,@StartStepId, @CategoryName, @OwnerLoginName
	,@NotifyLevelEventlog ,@NotifyLevelEmail ,@NotifyLevelNetsend ,@NotifyLevelPage
	,@NotifyEmailOperatorName ,@NotifyNetsendOperatorName ,@NotifyPageOperatorName
	,@DeleteLevel

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF NOT EXISTS(SELECT * FROM [msdb].dbo.syscategories WHERE ([name] = ISNULL(@CategoryName,N'[Uncategorized]')))
			EXEC [msdb].dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = @CategoryName

		IF NOT EXISTS(SELECT * FROM [msdb].dbo.sysjobs WHERE ([name] = @Name))
			BEGIN
				EXEC [msdb].dbo.sp_add_job @job_name = @Name
				EXEC [msdb].dbo.sp_add_jobserver @job_name = @Name, @server_name = @@SERVERNAME
			END
		ELSE
			BEGIN
				--preserve job status
				SELECT @Enabled = [enabled]
				FROM [msdb].dbo.sysjobs
				WHERE ([name] = @Name);
			END

		EXEC [msdb].dbo.sp_update_job @job_name = @Name
			,@enabled = @Enabled
			,@description = @Description
			,@start_step_id = @StartStepId
			,@category_name = @CategoryName 
			,@owner_login_name = @OwnerLoginName
			,@notify_level_eventlog = @NotifyLevelEventlog
			,@notify_level_email = @NotifyLevelEmail
			,@notify_level_netsend = @NotifyLevelNetsend
			,@notify_level_page = @NotifyLevelPage
			,@notify_email_operator_name = @NotifyEmailOperatorName
			,@notify_netsend_operator_name = @NotifyNetsendOperatorName
			,@notify_page_operator_name = @NotifyPageOperatorName
			,@delete_level = @DeleteLevel

		FETCH NEXT FROM C_JOBS INTO @Name ,@Enabled ,@Description ,@StartStepId, @CategoryName, @OwnerLoginName
			,@NotifyLevelEventlog ,@NotifyLevelEmail ,@NotifyLevelNetsend ,@NotifyLevelPage
			,@NotifyEmailOperatorName ,@NotifyNetsendOperatorName ,@NotifyPageOperatorName
			,@DeleteLevel
	END
	CLOSE C_JOBS
	DEALLOCATE C_JOBS

	-- ===============================================
	-- Refresh Job Steps
	-- Steps are first deleted and then re-added
	-- This allows the job history to be kept without
	-- needing the step_id to update correctly
	-- ===============================================
	DECLARE @JobName sysname, @StepId int
	SET @JobName = @Name
	
	IF(@AppendToJob = 0)
		BEGIN
			DECLARE C_STEPS_TO_DEL CURSOR FAST_FORWARD FOR(
				SELECT [step_id]
				FROM [msdb].dbo.sysjobsteps AS s
				INNER JOIN [msdb].dbo.sysjobs AS j
					ON (j.[job_id] = s.[job_id])
					AND(j.[name] = @JobName)
			)
			OPEN C_STEPS_TO_DEL	
			FETCH NEXT FROM C_STEPS_TO_DEL INTO @StepId
			WHILE(@@FETCH_STATUS = 0)
				BEGIN
					EXEC [msdb].dbo.sp_delete_jobstep @job_name = @JobName, @step_id = @StepId
					FETCH NEXT FROM C_STEPS_TO_DEL INTO @StepId
				END
			CLOSE C_STEPS_TO_DEL
			DEALLOCATE C_STEPS_TO_DEL
	END
	
	DECLARE @CleanJobName sysname, @SQLDataRoot nvarchar(256), @DefaultOutputFileName nvarchar(200);
	
	SET @CleanJobName = @JobName;
	EXEC [util].[RegExReplace] @input = @CleanJobName OUTPUT, @pattern = N'[>|:|-]', @replacement = N'';

	EXEC [util].[GetSQLDataRoot] @SQLDataRoot OUTPUT;
	
	SET @DefaultOutputFileName = @SQLDataRoot
		+ N'\LOG\Job_Output_' 
		+ REPLACE(@CleanJobName,N' ',N'_') 
		+ N'_$' + '(ESCAPE_NONE(STRTDT)).txt';

	DECLARE C_STEPS CURSOR FOR(
	SELECT * FROM OPENXML(@docHandle, N'/Root/Job/Step') 
	WITH (
		[JobName] sysname '../@Name'
		,[Server] nvarchar(128)
		,[StepId] int '@Id'
		,[StepName] sysname 'Name'
		,[Subsystem] nvarchar(40)
		,[Command] nvarchar(3200) 'Command'
		,[Flags] int
		,[OnSuccessAction] tinyint
		,[OnSuccessStepId] int
		,[OnFailAction] tinyint
		,[OnFailStepId] int
		,[DatabaseName] sysname
		,[RetryAttempts] int
		,[RetryInterval] int
		,[OutputFileName] nvarchar(200) 'OutputFileName'
	)
	)
	OPEN C_STEPS
	DECLARE @Server nvarchar(128) ,@StepName sysname ,@Subsystem nvarchar(40) ,@Command nvarchar(3200)
	,@Flags int ,@OnSuccessAction tinyint ,@OnSuccessStepId int ,@OnFailAction tinyint ,@OnFailStepId int
	,@DatabaseName sysname ,@RetryAttempts int ,@RetryInterval int ,@OutputFileName nvarchar(200)

	FETCH NEXT FROM C_STEPS INTO  @JobName ,@Server ,@StepId ,@StepName ,@Subsystem ,@Command
	,@Flags ,@OnSuccessAction ,@OnSuccessStepId ,@OnFailAction ,@OnFailStepId
	,@DatabaseName ,@RetryAttempts ,@RetryInterval ,@OutputFileName
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF NOT EXISTS(
			SELECT * FROM [msdb].dbo.sysjobsteps AS s
			INNER JOIN [msdb].dbo.sysjobs AS j
				ON (j.[job_id] = s.[job_id])
				AND(j.[name] = @JobName)
			WHERE (s.[step_name] = @StepName)
		)
			EXEC [msdb].dbo.sp_add_jobstep @job_name = @JobName ,@step_name = @StepName
			
		IF(@OutputFileName IS NULL)
			SET @OutputFileName = @DefaultOutputFileName;

		EXEC [msdb].dbo.sp_update_jobstep @job_name = @JobName
			,@server = @Server
			,@step_id = @StepId
			,@step_name = @StepName
			,@subsystem = @Subsystem 
			,@command = @Command
			,@on_success_action = @OnSuccessAction
			,@on_success_step_id = @OnSuccessStepId
			,@on_fail_action = @OnFailAction
			,@on_fail_step_id = @OnFailStepId
			,@database_name = @DatabaseName
			,@retry_attempts = @RetryAttempts
			,@retry_interval = @RetryInterval 
			,@output_file_name = @OutputFileName 
			,@flags = @Flags

		FETCH NEXT FROM C_STEPS INTO  @JobName ,@Server ,@StepId ,@StepName ,@Subsystem ,@Command
			,@Flags ,@OnSuccessAction ,@OnSuccessStepId ,@OnFailAction ,@OnFailStepId
			,@DatabaseName ,@RetryAttempts ,@RetryInterval ,@OutputFileName
	END
	CLOSE C_STEPS
	DEALLOCATE C_STEPS

	-- ===============================================
	-- Detach/Reattach Job Schedules
	-- ===============================================

	-- Detach all schedules currently associated with the job
	-- (delete any schedules which are no longer used as a result).
	DECLARE C_SCHEDULES CURSOR FAST_FORWARD FOR(
		SELECT
			s.[name]
		FROM [msdb].[dbo].[sysjobschedules] AS js
		INNER JOIN [msdb].[dbo].[sysjobs] AS j
			ON (js.[job_id] = j.[job_id])
		INNER JOIN [msdb].[dbo].[sysschedules] AS s
			ON (js.[schedule_id] = s.[schedule_id])
		WHERE (j.[name] = @JobName)
	);

	OPEN C_SCHEDULES;
	DECLARE @ScheduleName sysname;

	FETCH NEXT FROM C_SCHEDULES INTO @ScheduleName;
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		EXEC [msdb].[dbo].[sp_detach_schedule] @job_name = @JobName, @schedule_name = @ScheduleName, @delete_unused_schedule = 1;

		FETCH NEXT FROM C_SCHEDULES INTO @ScheduleName;
	END
	CLOSE C_SCHEDULES;
	DEALLOCATE C_SCHEDULES;

	-- Build the list of schedules to be associated with the job.
	DECLARE C_SCHEDULES CURSOR FOR(
		SELECT * FROM OPENXML(@docHandle, N'/Root/Job/Schedule') 
		WITH (
			[JobName] sysname '../@Name'
			,[ScheduleName] sysname '@Name'
			,[FreqType] int
			,[FreqInterval] int
			,[FreqSubdayType] int
			,[FreqSubdayInterval] int
			,[FreqRelativeInterval] int
			,[FreqRecurrenceFactor] int
			,[ActiveStartTime] int
			,[ActiveEndTime] int
		)
	)
	OPEN C_SCHEDULES
	DECLARE @ScheduleID int, @FreqType int, @FreqInterval int, @FreqSubdayType int, @FreqSubdayInterval int, @FreqRelativeInterval int, @FreqRecurrenceFactor int, @ActiveStartTime int ,@ActiveEndTime int;

	FETCH NEXT FROM C_SCHEDULES INTO @JobName, @ScheduleName, @FreqType, @FreqInterval, @FreqSubdayType, @FreqSubdayInterval, @FreqRelativeInterval, @FreqRecurrenceFactor, @ActiveStartTime, @ActiveEndTime;
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			DECLARE @JobId uniqueidentifier;
			SELECT @JobId = [job_id] FROM [msdb].[dbo].[sysjobs] WHERE ([name] = @JobName);

			-- Even though we have detached all schedules from the job we're modifying, 
			-- we still need to handle schedule updates as well as straight additions 
			-- because we might be re-attaching a shared schedule.
			-- If no schedule of that name exists, create it. Otherwise update it.
			IF NOT EXISTS(
				SELECT * FROM [msdb].[dbo].[sysschedules]
				WHERE ([name] = @ScheduleName)
			)
				BEGIN
					EXEC [msdb].[dbo].[sp_add_schedule] @schedule_name = @ScheduleName
						,@freq_type = @FreqType
						,@freq_interval = @FreqInterval
						,@freq_subday_type = @FreqSubdayType
						,@freq_subday_interval = @FreqSubdayInterval
						,@freq_relative_interval = @FreqRelativeInterval
						,@freq_recurrence_factor = @FreqRecurrenceFactor
						,@active_start_time = @ActiveStartTime
						,@active_end_time = @ActiveEndTime;
				END
			ELSE
				BEGIN
					EXEC [msdb].[dbo].[sp_update_schedule] @name = @ScheduleName
						,@freq_type = @FreqType
						,@freq_interval = @FreqInterval
						,@freq_subday_type = @FreqSubdayType
						,@freq_subday_interval = @FreqSubdayInterval
						,@freq_relative_interval = @FreqRelativeInterval
						,@freq_recurrence_factor = @FreqRecurrenceFactor
						,@active_start_time = @ActiveStartTime
						,@active_end_time = @ActiveEndTime;
				END

			-- Now we definitely have a matching, correctly defined schedule, attach it to the job.
			EXEC [msdb].[dbo].[sp_attach_schedule] @job_id = @JobID, @schedule_name = @ScheduleName;

			FETCH NEXT FROM C_SCHEDULES INTO @JobName, @ScheduleName, @FreqType, @FreqInterval, @FreqSubdayType, @FreqSubdayInterval, @FreqRelativeInterval, @FreqRecurrenceFactor, @ActiveStartTime, @ActiveEndTime;
		END
	CLOSE C_SCHEDULES
	DEALLOCATE C_SCHEDULES

	EXEC sp_xml_removedocument @docHandle

END